from tkinter import messagebox
import pymysql
import pymysql as sql
import datetime

class mysqlUse:
    def __init__(self):
        self.connect()

    def connect(self):
        # 创建数据库连接并将其保存为类属性
        self.conn = sql.connect(host='localhost', user='root', password='123456', db='primary_arithmetic0')
        if not self.conn:
            print("数据库连接失败")
            return

    def close(self):
        try:
            if self.conn:
                self.conn.close()
        except sql.Error as e:
            print('Error: %s' % e)

    def login_if_exist_user(self, user_name,user_password):
        """根据传入的用户名和用户密码参数，查找是否存在该用户，存在则返回用户id"""
        cur = self.conn.cursor()
        try:
            cur.execute('SELECT * FROM user WHERE username = %s AND password = %s', (user_name,user_password))
            result = cur.fetchone()
            if result:
                return result[0]  # 返回 id
            else:
                print("不存在该用户")
                return None  # 明确返回 None
        except sql.Error as e:
            self.conn.rollback()
            print(f"Error: {e}")
            messagebox.showerror(title='错误', message='查询失败')
            return None  # 明确返回 None
        finally:
            cur.close()

    def re_password(self, email, new_password):
        """根据传入的邮箱账号和更改的新密码，重置该用户密码"""
        cur = self.conn.cursor()
        print("213213213")
        try:
            cur.execute('UPDATE user SET password = %s WHERE email = %s', (new_password, email))
            print("213213213")
            self.conn.commit()
        except sql.Error as e:
            self.conn.rollback()
            print(f"Error: {e}")
            messagebox.showerror(title='错误', message='密码修改失败')
        finally:
            cur.close()

    def register(self,username,email,password,grade):
        """根据完善的信息录入数据库的用户表中"""
        cur = self.conn.cursor()
        try:
            current_time = datetime.datetime.now()
            cur.execute('INSERT INTO user(username, password, email,grade,create_time) VALUES(%s, %s, %s, %s, %s)',
                        (username, password,email,grade,current_time))
            self.conn.commit()
            messagebox.showinfo(title='恭喜', message='注册成功')
        except sql.Error as e:
            self.conn.rollback()
            print(f"Error: {e}")
        finally:
            cur.close()

    def email_exit(self,ema):
        """判断该邮箱账号的用户是否已经注册"""
        cur = self.conn.cursor()
        pan=0
        try:
            cur.execute('SELECT * FROM user WHERE email = %s', (ema))
            if cur.fetchone():
                pan=1
        except sql.Error as e:
            self.conn.rollback()
            print(f"Error: {e}")
        finally:
            cur.close()
            self.close()
            return pan

    def add_calculations(self,calc_id,difficulty,expression,answer):
        """添加算式到算式表"""
        cur = self.conn.cursor()
        try:
            current_time = datetime.datetime.now()
            cur.execute('INSERT INTO calculation(calc_id,difficulty,expression,answer) VALUES(%s, %s, %s, %s)',
                        (calc_id,difficulty,expression,answer))
            self.conn.commit()
            # messagebox.showinfo(title='恭喜', message='算式添加成功')
        except sql.Error as e:
            self.conn.rollback()
            print(f"Error: {e}")
        finally:
            cur.close()

    def Obtain_the_difficulty_calculations(self, difficulty_type, expression_quantity):
        """随机获取相应难度及相应数量的算式"""
        try:
            with self.conn.cursor() as cursor:
                query = """
                SELECT calc_id, expression,answer 
                FROM calculation
                WHERE difficulty = %s 
                ORDER BY RAND() 
                LIMIT %s
                """
                cursor.execute(query, (difficulty_type, expression_quantity))
                results = cursor.fetchall()
                return results
        except pymysql.MySQLError as e:
            print(f"Error fetching data: {e}")
            return []

    def add_exercise(self,difficulty, type, limit_time, quantity):
        """生成一套习题，并返回新插入习题的calc_id"""
        cur = self.conn.cursor()
        try:
            sql_query = 'INSERT INTO exercises (difficulty, type, limit_time, quantity) VALUES (%s, %s, %s, %s)'
            cur.execute(sql_query, (difficulty, type, limit_time, quantity))
            # 获取新插入记录的calc_id
            new_calc_id = cur.lastrowid
            self.conn.commit()
            return new_calc_id
        except pymysql.Error as e:
            self.conn.rollback()
            print(f"Error: {e}")
            return None
        finally:
            cur.close()

    def add_matches(self,exer_id,calc_id):
        """将生成的算式与习题依依匹配"""
        cur = self.conn.cursor()
        try:
            sql_query = 'INSERT INTO matches (exer_id,calc_id) VALUES (%s, %s)'
            cur.execute(sql_query, (exer_id,calc_id))
            self.conn.commit()
        except pymysql.Error as e:
            self.conn.rollback()
            print(f"Error: {e}")
            return None
        finally:
            cur.close()

    def add_practice(self, user_id, exer_id):
        print(user_id)
        print(exer_id)
        """将生成的算式与习题依依匹配"""
        cur = self.conn.cursor()
        start_time = datetime.datetime.now()
        try:
            sql_query = 'INSERT INTO practice (user_id, exer_id, start_time) VALUES (%s, %s, %s)'
            cur.execute(sql_query, (user_id, exer_id, start_time))
            # 使用 LAST_INSERT_ID() 函数获取自增 ID
            cur.execute("SELECT LAST_INSERT_ID()")
            result = cur.fetchone()
            new_prac_id = result[0]
            print(f"插入成功，新的练习记录 ID: {new_prac_id}")
            return new_prac_id
        except pymysql.MySQLError as e:
            self.conn.rollback()
            print(f"Error: {e}")
            return None
        finally:
            cur.close()

    def modify_practice(self, right_num, total, use_id):
        """提交习题后，完善使用表该记录的信息"""
        cur = self.conn.cursor()
        end_time = datetime.datetime.now()
        try:
            # 更新记录
            cur.execute('UPDATE practice SET end_time = %s, right_num = %s, total = %s WHERE use_id = %s',
                        (end_time, right_num, total, use_id))
            self.conn.commit()
            # 查询更新后的记录的start_time和end_time
            cur.execute('SELECT start_time, end_time FROM practice WHERE use_id = %s', (use_id,))
            result = cur.fetchone()  # 获取查询结果
            if result:
                start_time, end_time_from_db = result  # 从结果中解构出start_time和end_time
                return start_time, end_time_from_db  # 返回这两个值
            else:
                print(f"No record found for use_id: {use_id}")
                return None, None  # 如果没有找到记录，返回None

        except pymysql.Error as e:
            self.conn.rollback()
            print(f"Error: {e}")
            return None, None
        finally:
            cur.close()

    def add_wrongrecord(self,user_id,calc_id,wrong_answer):
        """将生成的算式与习题依依匹配"""
        cur = self.conn.cursor()
        submit_time=datetime.datetime.now()
        try:
            sql_query = 'INSERT INTO wrongrecord (user_id,calc_id,submit_time,wrong_answer) VALUES (%s, %s, %s, %s)'
            cur.execute(sql_query, (user_id,calc_id,submit_time,wrong_answer))
            self.conn.commit()
        except pymysql.Error as e:
            self.conn.rollback()
            print(f"Error: {e}")
        finally:
            cur.close()

    def add_wrongrecord_no_answer(self,user_id,calc_id):
        """将生成的算式与习题依依匹配"""
        cur = self.conn.cursor()
        submit_time=datetime.datetime.now()
        try:
            sql_query = 'INSERT INTO wrongrecord (user_id,calc_id,submit_time) VALUES (%s, %s, %s)'
            cur.execute(sql_query, (user_id,calc_id,submit_time))
            self.conn.commit()
        except pymysql.Error as e:
            self.conn.rollback()
            print(f"Error: {e}")
        finally:
            cur.close()

    def find_user_info(self, user_id):
        """根据用户id查找用户信息"""
        cur = self.conn.cursor(pymysql.cursors.DictCursor)  # 使用字典游标以便直接获取列名
        user_info = None  # 用于存储查询结果的变量
        try:
            cur.execute('SELECT * FROM user WHERE user_id = %s', (user_id,))
            result = cur.fetchone()  # 获取查询结果的第一行
            if result:
                user_info = result  # 如果查询到了结果，则将其存储在 user_info 变量中
        except pymysql.Error as e:
            self.conn.rollback()  # 如果发生错误，则回滚事务
            print(f"Error: {e}")
        finally:
            cur.close()  # 关闭游标
        return user_info  # 返回查询到的用户信息（如果有的话），否则返回 None

    def class_wrongrecord(self, user_id):
        """按照日期分类错题，得到各分类统计数目和具体算式 id，错误答案"""
        cur = self.conn.cursor(pymysql.cursors.DictCursor)  # 使用字典游标以便直接获取列名作为字典键
        try:
            # 执行查询，获取按日期和算式 ID 分类的错题统计
            cur.execute("""
                SELECT 
                    DATE_FORMAT(submit_time, '%%Y-%%m-%%d') AS submit_time0,
                    COUNT(*) AS error_count
                FROM 
                    wrongrecord
                WHERE 
                    user_id = %s
                GROUP BY 
                    submit_time0
            """, (user_id,))
            results = cur.fetchall()

            # 提取所有唯一的日期
            unique_dates = {result['submit_time0'] for result in results}
            unique_dates_list = sorted(unique_dates)  # 对日期进行排序，以便按时间顺序显示

        except pymysql.Error as e:
            self.conn.rollback()  # 如果发生错误，则回滚事务
            print(f"Error: {e}")
            results = []  # 在发生错误时，返回一个空列表
            unique_dates_list = []  # 在发生错误时，也返回一个空的日期列表

        finally:
            cur.close()  # 关闭游标

        # 返回两个值：查询结果和唯一日期列表
        return results, unique_dates_list

    def class_practice(self, user_id):
        cur = self.conn.cursor(pymysql.cursors.DictCursor)
        results = []
        practice_class_dates_list = []
        try:
            cur.execute("""
                SELECT 
                    DATE_FORMAT(end_time, '%%Y-%%m-%%d') AS class_end_date,
                    sum(right_num) AS class_right_num,
                    sum(total) AS class_total
                FROM 
                    practice
                WHERE 
                    user_id = %s
                    AND end_time IS NOT NULL
                GROUP BY 
                    class_end_date
                ORDER BY 
                    class_end_date
            """, (user_id,))
            results = cur.fetchall()
            print("查询结果:", results)  # 打印查询结果

            # 将结果转换为列表，并按照日期排序
            results = sorted(results, key=lambda x: x['class_end_date'])

            practice_class_dates = {result['class_end_date'] for result in results if
                                    result['class_end_date'] is not None}
            practice_class_dates_list = sorted(practice_class_dates)
            print("日期列表:", practice_class_dates_list)  # 打印日期列表

        except pymysql.Error as e:
            self.conn.rollback()
            print(f"Error: {e}")
            results = []
            practice_class_dates_list = []

        finally:
            cur.close()

        return results, practice_class_dates_list

    def get_wrongrecord_calculations(self, wrongrecord_submit_time, user_id):
        """根据错题时间来找到对应的算式"""
        cur = self.conn.cursor(pymysql.cursors.DictCursor)  # 使用字典游标以便直接获取列名作为字典键
        try:
            cur.execute("""
                SELECT 
                    wrongrecord.calc_id,
                    expression,
                    answer,
                    wrong_answer
                FROM 
                    wrongrecord,calculation
                WHERE 
                    wrongrecord.calc_id = calculation.calc_id and 
                    DATE_FORMAT(wrongrecord.submit_time, '%%Y-%%m-%%d') = %s
                    AND wrongrecord.user_id = %s
            """, (wrongrecord_submit_time, user_id))
            results = cur.fetchall()
        except pymysql.Error as e:
            self.conn.rollback()  # 如果发生错误，则回滚事务
            print(f"Error: {e}")
            results = []  # 在发生错误时，返回一个空列表
        finally:
            cur.close()  # 关闭游标
        return results
